SQL CASE

2024-07-11 17:04| 来源: 网络整理| 查看: 265

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.

In this lesson we'll cover:

The SQL CASE statement Adding multiple conditions to a CASE statement A quick review of CASE basics Using CASE with aggregate functions Using CASE inside of aggregate functions Practice problems

For the next few lessons, you'll work with data on College Football Players. This data was collected from ESPN on January 15, 2014 from the rosters listed on this page using a Python scraper available here. In this particular lesson, you'll stick to roster information. This table is pretty self-explanatory—one row per player, with columns that describe attributes for that player. Run this query to check out the raw data:

SELECT * FROM benn.college_football_players The SQL CASE statement

The CASE statement is SQL's way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements—SQL's equivalent of IF/THEN in Excel. Because of this pairing, you might be tempted to call this SQL CASE WHEN, but CASE is the accepted term.

Every CASE statement must end with the END statement. The ELSE statement is optional, and provides a way to capture values not specified in the WHEN/THEN statements. CASE is easiest to understand in the context of an example:

SELECT player_name, year, CASE WHEN year = 'SR' THEN 'yes' ELSE NULL END AS is_a_senior FROM benn.college_football_players

In plain English, here's what's happening:

The CASE statement checks each row to see if the conditional statement—year = 'SR' is true. For any given row, if that conditional statement is true, the word "yes" gets printed in the column that we have named is_a_senior. In any row for which the conditional statement is false, nothing happens in that row, leaving a null value in the is_a_senior column. At the same time all this is happening, SQL is retrieving and displaying all the values in the player_name and year columns.

The above query makes it pretty easy to see what's happening because we've included the CASE statement along with the year column itself. You can check each row to see whether year meets the condition year = 'SR' and then see the result in the column generated using the CASE statement.

But what if you don't want null values in the is_a_senior column? The following query replaces those nulls with "no":

SELECT player_name, year, CASE WHEN year = 'SR' THEN 'yes' ELSE 'no' END AS is_a_senior FROM benn.college_football_players Practice Problem

Write a query that includes a column that is flagged "yes" when a player is from California, and sort the results with those players first.

Try it out See the answer Adding multiple conditions to a CASE statement

You can also define a number of outcomes in a CASE statement by including as many WHEN/THEN statements as you'd like:

SELECT player_name, weight, CASE WHEN weight > 250 THEN 'over 250' WHEN weight > 200 THEN '201-250' WHEN weight > 175 THEN '176-200' ELSE '175 or under' END AS weight_group FROM benn.college_football_players

In the above example, the WHEN/THEN statements will get evaluated in the order that they're written. So if the value in the weight column of a given row is 300, it will produce a result of "over 250." Here's what happens if the value in the weight column is 180, SQL will do the following:

Check to see if weight is greater than 250. 180 is not greater than 250, so move on to the next WHEN/THEN Check to see if weight is greater than 200. 180 is not greater than 200, so move on to the next WHEN/THEN Check to see if weight is greater than 175. 180 is greater than 175, so record "175-200" in the weight_group column.

While the above works, it's really best practice to create statements that don't overlap. WHEN weight > 250 and WHEN weight > 200 overlap for every value greater than 250, which is a little confusing. A better way to write the above would be:

SELECT player_name, weight, CASE WHEN weight > 250 THEN 'over 250' WHEN weight > 200 AND weight 175 AND weight

【本文地址】

SQL CASE

SQL CASE

今日新闻

推荐新闻